Attribute VB_Name = "Inclusion_List_CSV_Export"
'Q-Exactive Inclusion List Export

'Option Private Module


Sub Export_CSV()


'VARIABLES


'BOOLEAN

Dim Active As Boolean


'INTEGERS

Dim First_summary_row As Integer
Dim ExportRow As Integer
Dim DataRow As Integer


'NUMBERS

Dim End_time As Single
Dim RT As Single
Dim Start_time As Single
Dim Window As Single



'REFERENCES

Dim ExportSheet As Worksheet
Dim DataBook As Workbook
Dim DataSheet As Worksheet
Dim DataTable As ListObject
Dim NameCell As Range


'STRINGS

Dim File_name As String




'SET INITIAL VARIABLES

Application.ScreenUpdating = False  'TURN OFF SCREEN UPDATING TO INCREASE SPEED




'SET REFERENCES

Set DataBook = ThisWorkbook
Set DataSheet = ThisWorkbook.Worksheets(1)
Set DataTable = DataSheet.ListObjects("Table4")


'GET SAVE PATH FROM USER

ChDrive "C"
'ChDir "C:\Xcalibur\Methods\"

'File_name = "Inclusion_List_" & Format(Now(), "mmddyyhhmm") & ".csv"
File_name = Application.GetSaveAsFilename("QE_Inclusion_List_" & Format(Now(), "mmddyyhhmm") & ".csv", "Comma Separated Files (*.csv),*.csv", 0, "Save Sequence .csv File")
If File_name = "False" Then Exit Sub  'EXIT MACRO WITHOUT SAVING IF USER SELECTS CANCEL



'CREATE A NEW WORKBOOK AND SET REFERENCE

Application.DisplayAlerts = False
Set ExportBook = Workbooks.Add
    With ExportBook
        .Worksheets(1).Name = "Inclusion_List_" & Format(Now(), "mmddyyhhmm")
        .SaveAs Filename:=File_name, FileFormat:=xlCSV
    End With
Application.DisplayAlerts = True


Set ExportSheet = ExportBook.Worksheets(1)



'COPY SCAN DATA FROM SHEET Summary SHEET TO METHOD SHEET

ExportRow = 1
DataRow = 1


'ENTER HEADERS


ExportSheet.Cells(ExportRow, 1) = "Mass [m/z]"
ExportSheet.Cells(ExportRow, 2) = "Formula [M]"
ExportSheet.Cells(ExportRow, 3) = "Formula type"
ExportSheet.Cells(ExportRow, 4) = "Species"
ExportSheet.Cells(ExportRow, 5) = "CS [z]"
ExportSheet.Cells(ExportRow, 6) = "Polarity"
ExportSheet.Cells(ExportRow, 7) = "Start [min]"
ExportSheet.Cells(ExportRow, 8) = "End [min]"
ExportSheet.Cells(ExportRow, 9) = "NCE"
ExportSheet.Cells(ExportRow, 10) = "Comment"

ExportRow = ExportRow + 1


'ONLY EXPORT UNFILTERED, VISIBLE ROWS

For Each NameCell In DataTable.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

DataRow = NameCell.Row
DataRow = NameCell.Row - 6


'COPY COMPOUND DATA FROM EXCEL DATA TABLE

ExportSheet.Cells(ExportRow, 1) = DataTable.ListColumns("Adduct m/z").Range(DataRow)
ExportSheet.Cells(ExportRow, 2) = DataTable.ListColumns("Formula").Range(DataRow)
ExportSheet.Cells(ExportRow, 3) = "Chemical formula"                   'FORMULA TYPE
ExportSheet.Cells(ExportRow, 4) = ""                                   'SPECIES
ExportSheet.Cells(ExportRow, 5) = ""                                   'CS
ExportSheet.Cells(ExportRow, 6) = "Positive"                           'POLARITY
ExportSheet.Cells(ExportRow, 7) = DataTable.ListColumns("Start Time").Range(DataRow)
ExportSheet.Cells(ExportRow, 8) = DataTable.ListColumns("End Time").Range(DataRow)
ExportSheet.Cells(ExportRow, 9) = DataTable.ListColumns("NCE").Range(DataRow)
ExportSheet.Cells(ExportRow, 10) = DataTable.ListColumns("Compound").Range(DataRow)


ExportRow = ExportRow + 1

Next NameCell



'SAVE AND CLOSE .CSV FILE

ExportBook.Save
ExportBook.Close Savechanges = False

MsgBox "Instrument method export file saved as " & File_name, vbInformation


End Sub



